package dao;

import domain.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.JDBCUtils;

import java.util.List;

public class Dao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    public int register_User(User user) {
        try {
            String sql = "insert into user values(null,?,?,?,?,?)";
            int update = jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getGender(), user.getPhone(), user.getAddress());
            return update;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int register_Administrators(Administrators administrators) {
        try {
            String sql = "insert into administrators values(null,?,?,?,?,?)";
            int update = jdbcTemplate.update(sql, administrators.getUsername(), administrators.getPassword(), administrators.getGender(), administrators.getPhone(), administrators.getAddress());
            return update;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public User login_user(String username, String password) {
        try {
            String sql = "select * from user where username = ? and password = ?";
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Administrators login_administrators(String username, String password) {
        try {
            String sql = "select * from administrators where username = ? and password = ?";
            Administrators administrators = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Administrators>(Administrators.class), username, password);
            return administrators;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<User> findAllUser() {
        String sql = "select * from user";
        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    public void deleteUserById(int id) {
        String sql = "delete from user where id = ?";
        jdbcTemplate.update(sql, id);
    }

    public int addGoods(Goods goods) {
        String sql = "insert into goods(cid,cname,cname_no1,cname_no2,cnumber,cprice,cintro,cimage,cdiscounte) values (?,?,?,?,?,?,?,?,?)";
        int update = jdbcTemplate.update(sql, goods.getCid(), goods.getCname(), goods.getCname_no1(), goods.getCname_no2(), goods.getCnumber(), goods.getCprice(), goods.getCintro(), goods.getCimage(), goods.getCdiscounte());
        return update;
    }

    public List<Goods> findAllGoods() {
        String sql = "select * from goods";
        List<Goods> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Goods>(Goods.class));
        return query;
    }

    public Goods findGoodByCid(String cid) {
        String sql = "select * from goods where cid = ?";
        Goods goods = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Goods>(Goods.class), cid);
        return goods;
    }

    public int updateGoods(Goods goods) {
        String sql = "update goods set cname = ?,cname_no1 = ?,cname_no2 = ?,cnumber = ?,cprice = ?,cintro = ?,cimage = ?,cdiscounte = ? where cid = ?";
        int update = jdbcTemplate.update(sql, goods.getCname(), goods.getCname_no1(), goods.getCname_no2(), goods.getCnumber(), goods.getCprice(), goods.getCintro(), goods.getCimage(), goods.getCdiscounte(), goods.getCid());
        return update;
    }

    public void deleteGoodByCid(String cid) {
        String sql = "delete from goods where cid = ?";
        jdbcTemplate.update(sql, cid);
    }

    public List<Goods> findGoodsBySearch(String cname_no1) {
        String sql = "select * from goods where cname_no1 = ?";
        List<Goods> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Goods>(Goods.class), cname_no1);
        return query;
    }

    public User findUserById(int id) {
        String sql = "select * from user where id = ?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }

    public int updateUser(User user) {
        String sql = "update user set username = ?,gender = ?,phone = ?,address = ? where id = ?";
        int update = jdbcTemplate.update(sql, user.getUsername(), user.getGender(), user.getPhone(), user.getAddress(), user.getId());
        return update;
    }

    public ShoppingCar findGoodFromCar(String sid, int suserid) {
        String sql = "select * from shoppingcar where sid = ? and suserid = ?";
        ShoppingCar shoppingCar = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<ShoppingCar>(ShoppingCar.class), sid, suserid);
        return shoppingCar;
    }

    public int addShoppingCar(Goods goods, User user, int cnumber, float stotalprice) {
        try {
            ShoppingCar goodFromCar = findGoodFromCar(goods.getCid(), user.getId());
            String sql = "update shoppingcar set snumber = ?,stotalprice = ? where sid = ? and suserid = ?";
            int update = jdbcTemplate.update(sql, goodFromCar.getSnumber() + cnumber, (goodFromCar.getSnumber() + cnumber) * (goods.getCprice() * goods.getCdiscounte() / 10), goodFromCar.getSid(), goodFromCar.getSuserid());
            return update;
        } catch (Exception e) {
            String sql = "insert into shoppingcar(sid,suserid,snumber,stotalprice) values(?,?,?,?)";
            int update = jdbcTemplate.update(sql, goods.getCid(), user.getId(), cnumber, stotalprice);
            return update;
        }
    }

    public List<Car_User_Good> findGoodFromCarById(int id) {
        String sql = "SELECT shoppingcar.sid,shoppingcar.suserid,shoppingcar.snumber,shoppingcar.stotalprice,goods.cname,goods.cname_no1,`user`.username FROM (shoppingcar JOIN goods ON shoppingcar.sid = goods.cid) JOIN `user` ON shoppingcar.suserid = `user`.id WHERE shoppingcar.suserid = ?;";
        List<Car_User_Good> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Car_User_Good>(Car_User_Good.class), id);
        return query;
    }

    public void updateGoodNumber(String sid, int suserid, int snumber) {
        ShoppingCar goodFromCar = findGoodFromCar(sid, suserid);
        String sql = "update shoppingcar set snumber = ?,stotalprice = ? where sid = ? and suserid = ?";
        jdbcTemplate.update(sql, snumber, (snumber * (goodFromCar.getStotalprice() / goodFromCar.getSnumber())), sid, suserid);
    }

    public int delAllGoodFromCar(int suserid) {
        String sql = "delete from shoppingcar where suserid = ?";
        int update = jdbcTemplate.update(sql, suserid);
        return update;
    }

    public int delGoodFromCarById(String sid, int suserid) {
        String sql = "delete from shoppingcar where sid = ? and suserid = ?";
        int update = jdbcTemplate.update(sql, sid, suserid);
        return update;
    }

    public int updateGoodNumberByOrder(Order order) {
        Goods goodByCid = findGoodByCid(order.getSid());
        String sql = "update goods set cnumber = ? where cid = ?";
        int update = jdbcTemplate.update(sql, (goodByCid.getCnumber() - order.getSnumber()), order.getSid());
        return update;
    }

    public int addOrder(Order order) {
        System.out.println(order);
        if (updateGoodNumberByOrder(order) > 0) {
            String sql = "insert into `order`(orderid,sid,suserid,orderdate,orderstate,address,receivename,tele,paymode,logisticsstatus,snumber,ordertotal) values(?,?,?,?,?,?,?,?,?,?,?,?)";
            int update = jdbcTemplate.update(sql, order.getOrderid(), order.getSid(), order.getSuserid(), order.getOrderdate(), order.getOrderstate(), order.getAddress(), order.getReceivename(), order.getTele(), order.getPaymode(), order.getLogisticsstatus(), order.getSnumber(), order.getOrdertotal());
            return update;
        } else {
            return 0;
        }
    }

    public List<Order_Good> findAllOrder(int suserid) {
        String sql = "SELECT\n" +
                "\t`order`.orderid,\n" +
                "\t`order`.sid,\n" +
                "\t`order`.suserid,\n" +
                "\t`order`.orderstate,\n" +
                "\t`order`.address,\n" +
                "\t`order`.receivename,\n" +
                "\t`order`.tele,\n" +
                "\t`order`.paymode,\n" +
                "\t`order`.logisticsstatus,\n" +
                "\t`order`.snumber,\n" +
                "\t`order`.ordertotal,\n" +
                "\tgoods.cname,\n" +
                "\tgoods.cimage\n" +
                "FROM\n" +
                "\t`order`\n" +
                "JOIN goods ON `order`.sid = goods.cid\n" +
                "WHERE\n" +
                "\t`order`.suserid = ?";
        List<Order_Good> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Order_Good>(Order_Good.class), suserid);
        return query;
    }

    public List<Order_Good> findOrders() {
        String sql = "SELECT\n" +
                "\t`order`.orderid,\n" +
                "\t`order`.sid,\n" +
                "\t`order`.suserid,\n" +
                "\t`order`.orderstate,\n" +
                "\t`order`.address,\n" +
                "\t`order`.receivename,\n" +
                "\t`order`.tele,\n" +
                "\t`order`.paymode,\n" +
                "\t`order`.logisticsstatus,\n" +
                "\t`order`.snumber,\n" +
                "\t`order`.ordertotal,\n" +
                "\tgoods.cname,\n" +
                "\tgoods.cimage\n" +
                "FROM\n" +
                "\t`order`\n" +
                "JOIN goods ON `order`.sid = goods.cid";
        List<Order_Good> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Order_Good>(Order_Good.class));
        return query;
    }

    public Order_Good findOrderByOrderId(String orderid) {
        String sql = "SELECT\n" +
                "\t`order`.orderid,\n" +
                "\t`order`.sid,\n" +
                "\t`order`.suserid,\n" +
                "\t`order`.orderstate,\n" +
                "\t`order`.address,\n" +
                "\t`order`.receivename,\n" +
                "\t`order`.tele,\n" +
                "\t`order`.paymode,\n" +
                "\t`order`.logisticsstatus,\n" +
                "\t`order`.snumber,\n" +
                "\t`order`.ordertotal,\n" +
                "\tgoods.cname,\n" +
                "\tgoods.cimage\n" +
                "FROM\n" +
                "\t`order`\n" +
                "JOIN goods ON `order`.sid = goods.cid\n" +
                "WHERE\n" +
                "\t`order`.orderid = ?";
        Order_Good order_good = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Order_Good>(Order_Good.class), orderid);
        return order_good;
    }

    public void updateLogistics(String orderid) {
        String sql = "update `order` set logisticsstatus = \"已发货\" where orderid = ?";
        jdbcTemplate.update(sql, orderid);
    }

    public int updateOrderInfo(String address, String receivename, String tele, String orderid) {
        String sql = "update `order` set address = ?,receivename = ?,tele = ? where orderid = ?";
        int update = jdbcTemplate.update(sql, address, receivename, tele, orderid);
        return update;
    }
}
